﻿ alter table Cms_Wsm_Conservation
add OrderNum int


alter table  Cms_Wsm_MaintainEvaluation
add Evaluationdesc varchar(2000)

alter table  Cms_Wsm_MaintainEvaluation
add IsTrack int



GO

CREATE TABLE [dbo].[Cms_Wsm_Component](
	[ComponentID] [uniqueidentifier] NOT NULL,
	[ComponentName] [nvarchar](128) NULL,
	[ComponentDesc] [nvarchar](2000) NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[CreatedOn] [datetime] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_Cms_Wsm_Component] PRIMARY KEY CLUSTERED 
(
	[ComponentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


GO

CREATE TABLE [dbo].[Cms_Wsm_ComponentInstrument](
	[ID] [uniqueidentifier] NOT NULL,
	[InstrumentID] [uniqueidentifier] NULL,
	[ComponentID] [uniqueidentifier] NULL,
	Deleted bit null,
 CONSTRAINT [PK_Cms_Wsm_ComponentInstrument] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


GO

CREATE TABLE [dbo].[Cms_Wsm_CustomerContactor](
	[ContactorID] [uniqueidentifier] NOT NULL,
	[ContactorName] [nvarchar](128) NULL,
	[ContactorDuty] [nvarchar](50) NULL,
	[ContactorPhone] [nvarchar](50) NULL,
	[ContactorEmal] [nvarchar](50) NULL,
	[CustomerID] [uniqueidentifier] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_Cms_Wsm_CustomerContactor] PRIMARY KEY CLUSTERED 
(
	[ContactorID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


alter table Cms_Wsm_AccessoryReplacement add AccssoryCode varchar(100) null --
alter table Cms_Wsm_AccessoryReplacement add PersonalStockQuantity int null --个人库存
alter table Cms_Wsm_AccessoryReplacement add Remark varchar(500) --下定备注

go

--添加工作台 -当天的工作注册表
insert into Cms_Sys_Webparts values(NEWID(),'000040','0000029902','/Workspace/TodayWorkRegistList.aspx',300,'当天的工作注册表','4230BC6E-69E6-46A9-A39E-B929A06A84E8',GETDATE(),null,null,'当天的工作注册表',null)

--添加工作台 -值日表
insert into Cms_Sys_Webparts values(NEWID(),'000041','0000029902','/NoticeManage/DESKTOP_DutyList.aspx',300,'值日表','4230BC6E-69E6-46A9-A39E-B929A06A84E8',GETDATE(),null,null,'值日表',null)

go


alter table Cms_Wsm_AccessoryInfo
add LowStockQuantity decimal(18,2) not null default(0),		--最低库存量
	FactoryInstrumentCode varchar(30) null					--配件、易耗件的厂家编号
	
go

ALTER view [dbo].[vw_StockQuery]  --配件库存查询
as
select a.AccessoryID,a.CompanyInstrumentCode,a.AccessoryName,a.Unit,a.PresellPrice,a.FactoryInstrumentCode,a.LowStockQuantity,a.AccessoryStandard
,(si.InQuantity - ISNULL(so.Outquantity,0)) as coStockquantity
,t.Receiver
,t.PersonalStockquantity
from dbo.Cms_Wsm_AccessoryInfo a with(nolock)
inner join 
(
select AccessoryID,sum(quantity) as InQuantity from Cms_Wsm_AccessoryStock with(nolock) group by AccessoryID
) si on si.AccessoryID = a.AccessoryID
left join
(
	select AccessoryID,SUM(quantity) as Outquantity from Cms_Wsm_StockInOut with(nolock) group by AccessoryID
) so on so.AccessoryID = si.AccessoryID
left join	--个人库存
(
select
a.AccessoryID
,a.Receiver
,(a.quantity-ISNULL(b.quantity,0)) as PersonalStockquantity		--个人库存数量
from
(
select Receiver,AccessoryID,sum(quantity) as quantity from Cms_Wsm_StockInOut with(nolock) group by Receiver,AccessoryID
) a
left join 
(
select bill.Maintainer,ar.AccessoryID,SUM(ar.ReplacementNum) as quantity from Cms_Wsm_MaintainBill bill with(nolock) 
inner join Cms_Wsm_AccessoryReplacement ar with(nolock) on ar.MaintainID = bill.MaintainID
group by bill.Maintainer,ar.AccessoryID
) b on a.Receiver = b.Maintainer and a.AccessoryID = b.AccessoryID
) t on t.AccessoryID = a.AccessoryID


--公告部门表
create table Cms_Ctrt_MyNoticeDepartment
(
ID uniqueidentifier primary key default(newid()),
NoticeID uniqueidentifier null,
DepartmentID uniqueidentifier null,
DepartmentName varchar(50) null,
Deleted bit null
)